package root.service;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.SqlPara;
import root.utils.StringHelper;

import java.util.List;

/**
 * @author 吕梁山
 * @date 2018/9/26
 */
public class SystemService extends JdbcTemplate{

    /**
     * 获取首页内容
     */
    public Record index(){
       StringBuilder stringBuilder = new StringBuilder();
       stringBuilder.append(" select userCount,productCount,registerCount,money from ( ");
       stringBuilder.append(" (SELECT count(0) userCount from t_user ) as userCount ");
       stringBuilder.append(" ,(select count(0) productCount from t_product ) as productCount ");
       stringBuilder.append(" ,(select count(0) registerCount from t_product_referer_register where status = 1 ) as registerCount ");
       stringBuilder.append(" ,(select IFNULL((count(0) * p.money_base),0) money from t_product_referer_register r,t_product p where r.status = 1 and r.product_id = p.id) as money ");
       stringBuilder.append(" ) ");
       return Db.findFirst(stringBuilder.toString());
    }

    /**
     * 分页获取后台用户
     */
    public Page<Record> getManagerUserPage(String tempKey, String tempValue, int pageNumber, int pageSize) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select * from t_manager_user where status = 0");
        if(StringHelper.isNotEmpty(tempKey) && StringHelper.isNotEmpty(tempValue)){
            if("1".equals(tempKey)){
                stringBuilder.append("and realname = '" + tempValue + "'");
            }else if("2".equals(tempKey)){
                stringBuilder.append("and phone = '" + tempValue + "'");
            }
        }
        stringBuilder.append(" order by create_date desc");
        return Db.paginate(pageNumber, pageSize, new SqlPara().setSql(stringBuilder.toString()));
    }

    /**
     * 重置用户密码
     */
    public int resetUserPwd(String userId, String password) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("update t_manager_user set password = '" + password + "' where id = " + userId);
        return Db.update(stringBuilder.toString());
    }

    /**
     * 新增后台用户
     */
    public void addManagerUser(Record user) {
        Db.save("t_manager_user", user);
    }

    /**
     * 分页获取字典类型
     */
    public Page<Record> getDictTypePage(String dictName, String dictType, String status, int pageNumber, int pageSize) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select t.id, t.dict_name, t.dict_type, t.status, t.description, t.create_date, u.realname create_by from t_manager_dict_type t,t_manager_user u where u.id = t.create_by");
        if(StringHelper.isNotEmpty(dictName)){
            stringBuilder.append(" and t.dict_name = '" + dictName + "'");
        }
        if(StringHelper.isNotEmpty(dictType)){
            stringBuilder.append(" and t.dict_type = '" + dictType + "'");
        }
        if(StringHelper.isNotEmpty(status)){
            stringBuilder.append(" and t.status = '" + status + "'");
        }
        stringBuilder.append(" order by t.id desc");
        return Db.paginate(pageNumber, pageSize, new SqlPara().setSql(stringBuilder.toString()));
    }

    /**
     * 新增字典类型
     */
    public void saveDictType(Record record) {
        Db.save("t_manager_dict_type", record);
    }

    /**
     * 新增字典类型
     */
    public void updateDictType(Record record) {
        Db.update("t_manager_dict_type", record);
    }

    /**
     * 获取单条字典类型
     */
    public Record getDictType(String dictTypeId) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select * from t_manager_dict_type where id = " + dictTypeId);
        return Db.findFirst(stringBuilder.toString());
    }

    /**
     * 分页获取字典数据
     */
    public Page<Record> getDictDataPage(String dictType, String status, int pageNumber, int pageSize) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select t.id, t.dict_value, t.dict_label, t.dict_type, t.css_class, t.status, t.description, t.create_date, u.realname create_by from t_manager_dict_data t,t_manager_user u where u.id = t.create_by");
        if(StringHelper.isNotEmpty(dictType)){
            stringBuilder.append(" and t.dict_type = '" + dictType + "'");
        }
        if(StringHelper.isNotEmpty(status)){
            stringBuilder.append(" and t.status = '" + status + "'");
        }
        stringBuilder.append(" order by t.id desc");
        return Db.paginate(pageNumber, pageSize, new SqlPara().setSql(stringBuilder.toString()));
    }

    /**
     * 新增字典数据
     */
    public void saveDictData(Record record) {
        Db.save("t_manager_dict_data", record);
    }

    /**
     * 新增字典数据
     */
    public void updateDictData(Record record) {
        Db.update("t_manager_dict_data", record);
    }

    /**
     * 获取单条字典数据
     */
    public Record getDictData(String dictDataId) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select * from t_manager_dict_data where id = " + dictDataId);
        return Db.findFirst(stringBuilder.toString());
    }

    /**
     * 通过dict_type查询字典表
     */
    public Record getDictTypeByType(String dictType) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select * from t_manager_dict_type where dict_type = '" + dictType + "'");
        return Db.findFirst(stringBuilder.toString());
    }

    /**
     * 通过dict_type和dict_value查询字典数据表
     */
    public Record getDictDataByDictTypeAndDictValue(String dictType, String dictValue) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select * from t_manager_dict_data where dict_type = '" + dictType + "' and dict_value = " + dictValue);
        return Db.findFirst(stringBuilder.toString());
    }

    /**
     * 获取所有字典数据
     */
    public List<Record> getAllDictDataList() {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select dict_type,dict_label, dict_value,css_class from t_manager_dict_data where status = 0 order by dict_value");
        return Db.find(stringBuilder.toString());
    }

    /**
     * 获取所有字典类型
     */
    public List<Record> getAllDictTypeList() {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select dict_type, dict_name from t_manager_dict_type where status = 0 order by id");
        return Db.find(stringBuilder.toString());
    }

    /**
     * 获取邀请榜前十
     */
    public List<Record> getRankByUser() {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SELECT IFNULL(u.realname,u.username) name,count(0) value from t_product_referer_register r,t_user u where u.id = r.user_id GROUP BY r.user_id ORDER BY value desc limit 10 ");
        return Db.find(stringBuilder.toString());
    }

    /**
     * 获取产品榜前5
     */
    public List<Record> getRankByProduct() {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SELECT p.item_name name,count(0) value from t_product_referer_register r,t_product p where p.id = r.product_id GROUP BY r.product_id ORDER BY value desc limit 10 ");
        return Db.find(stringBuilder.toString());
    }

    /**
     * 获取数据统计
     */
    public List<Record> getDataCount() {
        String sql = "select time,apply_count,effective_count,user_count,in_money,out_money,money from t_system_statistics order by time";
        return Db.find(sql);
    }

    public Record getManagerUserInfo(int userId) {
        String sql = " select id,username,password,realname,phone from t_manager_user where id = " + userId;
        return Db.findFirst(sql);
    }

    /**
     * 更新个人信息
     */
    public boolean updateManagerUserInfo(Record record) {
       return Db.update("t_manager_user", "id", record);
    }

    /**
     * 分页获取系统配置信息
     */
    public Page<Record> getSystemConfigPage(String status, int pageNumber, int page_size_15) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select c.*, u.realname create_by from t_system_config c,t_manager_user u where c.create_by = u.id ");
        if(StringHelper.isNotEmpty(status)){
            stringBuilder.append(" and status = " + status);
        }
        stringBuilder.append(" order by create_date desc ");
        return Db.paginate(pageNumber, page_size_15, new SqlPara().setSql(stringBuilder.toString()));
    }

    /**
     * 获取最新的系统参数
     */
    public Record getLastSystemConfig() {
        String sql = "select * from t_system_config where status = 0 order by id desc limit 1";
        return Db.findFirst(sql);
    }
}
